SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.DnnProduct_GetSearchResultsByWord
	@PortalID	int,
	@Word		nvarchar(100)
AS
    SELECT si.SearchItemID,
	    sw.Word,
	    siw.Occurrences,
	    siw.Occurrences + 1000 AS Relevance,
	    m.ModuleID,
	    tm.TabID,
		si.Title, 
	    si.Description,
	    si.Author,
	    si.PubDate,
	    si.SearchKey,
	    si.Guid,
	    si.ImageFileId,
	    u.FirstName + ' ' + u.LastName As AuthorName,
	    m.PortalId
    FROM    dbo.DnnProduct_SearchWord sw
	    INNER JOIN dbo.DnnProduct_SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID
	    INNER JOIN dbo.DnnProduct_SearchItem si ON siw.SearchItemID = si.SearchItemID
	    INNER JOIN dbo.DnnProduct_Modules m ON si.ModuleId = m.ModuleID
	    LEFT OUTER JOIN dbo.DnnProduct_TabModules tm ON si.ModuleId = tm.ModuleID
		LEFT JOIN dbo.DnnProduct_TabModuleSettings tms ON tms.TabModuleID = tm.TabModuleID AND tms.SettingName = 'DisableSearch'
	    INNER JOIN dbo.DnnProduct_Tabs t ON tm.TabID = t.TabID
	    LEFT OUTER JOIN dbo.DnnProduct_Users u ON si.Author = u.UserID
    WHERE   (((m.StartDate Is Null) OR (GetDate() > m.StartDate)) AND ((m.EndDate Is Null) OR (GetDate() < m.EndDate)))
	    AND (((t.StartDate Is Null) OR (GetDate() > t.StartDate)) AND ((t.EndDate Is Null) OR (GetDate() < t.EndDate)))
	    AND (sw.Word = @Word) 
	    AND (t.IsDeleted = 0) 
	    AND (m.IsDeleted = 0)
		AND (tm.IsDeleted = 0)
	    AND (t.PortalID = @PortalID)
		AND (tms.TabModuleID is NULL)
    ORDER BY Relevance DESC
GO
